ALTER PROCEDURE sp_cat_GetPartMaster
@PKPartMaster INT = NULL, @PartNumber VARCHAR(50) = NULL , @ManufacturerPartNumber VARCHAR(50) = NULL , 
@Description VARCHAR(200) = NULL , @Distributor VARCHAR(50) = NULL , @Manufacturer VARCHAR(50) = NULL
AS
BEGIN

  SELECT 
  PM.PKPartMaster , PM.PartNumber, PM.ManufacturerPartNumber , PM.FKManufacturer, PM.FKDistributor,
  PM.Adders, '<a href="'+PM.DataSheet+'">ver</a>' DataSheetLink , PM.DataSheet,   PM.Description , 
  PM.FKStatus, '<a href="'+PM.Photo+'">ver</a>' PhotoLink, PM.Photo, PM.PurchacePrice , PM.PurchaseQty, 
  '<a href="'+PM.Quote+'">ver</a>' QuoteLink, PM.Quote, PM.SalesPrice , PM.SalesQty , D.Distributor , 
  M.Manufacturer ,  PM.PartNumber + ' ('+PM.ManufacturerPartNumber+'): ' +  PM.Description AS searchRes
  FROM CT_PartMaster PM
  INNER JOIN CT_Distributor D ON PM.FKDistributor = D.PKDistributor
  INNER JOIN CT_Manufacturer M ON PM.FKManufacturer = M.PKManufacturer
  INNER JOIN CT_Status S ON PM.FKStatus = S.PKStatus
  WHERE PM.PKPartMaster = COALESCE( @PKPartMaster , PM.PKPartMaster )
  AND (
 
  CASE WHEN @PartNumber IS NULL THEN 1 WHEN PM.PartNumber LIKE '%'+@PartNumber+'%'  THEN 1 ELSE 0 END = 1
  OR CASE WHEN @ManufacturerPartNumber IS NULL THEN 1 WHEN PM.ManufacturerPartNumber LIKE '%'+@ManufacturerPartNumber+'%'  THEN 1 ELSE 0 END = 1
  OR CASE WHEN @Description IS NULL THEN 1 WHEN PM.Description LIKE '%'+@Description+'%'  THEN 1 ELSE 0 END = 1
)
  ORDER BY
  PM.PartNumber
END
GO 